﻿using System;
using System.Collections.Generic; 
using System.Text;
using System.Data;
using Titan.ExpressionAnalyse; 
using System.Data.Common;
using System.Collections;

namespace Titan
{
    public abstract class SqlProvider : ISqlProvider  
    {

        private IAttributeReader attributeReader;
        private readonly Dictionary<EntityType, ITable> tables = new Dictionary<EntityType, ITable>();//缓存,FullEntityName作为键，
        private readonly Dictionary<EntityType, IStatement> statements = new Dictionary<EntityType, IStatement>(); //缓存

        public Dictionary<EntityType, ITable> Tables
        {
            get { return tables; }
        }
        public Dictionary<EntityType, IStatement> Statements
        {
            get { return statements; }
        }

        public SqlProvider()
        {
            attributeReader=CreateAttributeReader();
        }

        #region 子类必须实现的方法

        public abstract IDbConnection CreateConnection(); 
        public abstract DbDataAdapter CreateDataAdapter(); 
        public abstract void WrapPaging(IDbSession session, IDbCommand command, int pageSize, int pageIndex); 
        public abstract object ConvertDbValue(object value, Type expectedType);


        public abstract int Insert(IDbSession session, IDictionary<string, string> tableMapping, EntityType entityType, INameValueExpressionCollection nameValues, object entity);
        public abstract int Update(IDbSession session, IDictionary<string, string> tableMapping, EntityType entityType, IUpdateExpression updateExpression);
        public abstract int Delete(IDbSession session, IDictionary<string, string> tableMapping, EntityType entityType, IConditionCollection conditionCollection);
        public abstract int SelectCollection(IDbSession session, IDictionary<string, string> tableMapping, EntityType entityType, IList list, IQueryExpression queryExpression, bool clearListBeforeFill);
        public abstract int SelectCount(IDbSession session, IDictionary<string, string> tableMapping, EntityType entityType, IConditionCollection conditionCollection);
        public abstract bool Exists(IDbSession session, IDictionary<string, string> tableMapping, EntityType entityType, IConditionCollection conditionCollection);



        protected abstract IDataParameter CreateParameter(string parameterName, object parameterValue, Type valueType, int length);

        #endregion 

        #region LoadMapping
        protected abstract IAttributeReader CreateAttributeReader(); 

        #endregion

        #region 已实现的接口中的部分方法


        public virtual ITable GetTableByEntityType(EntityType entityType)
        {
            if (!tables.ContainsKey(entityType))
            {
                lock (tables)
                {
                    if (!tables.ContainsKey(entityType))
                    {
                        ITable table = attributeReader.ReadTableAttribute(entityType);
                        if(table==null){
                            throw ExceptionFactory.AttributeNotFoundException(entityType.Type, typeof(TableAttribute));
                        }
                        //必须要查找出RelationColumn的ColumnName 
                        foreach (IRelation relation in table.Relations.Values)
                        {
                            foreach (IRelationColumn relationColumn in relation.RelationColumns)
                            {
                                string columnName = table.Columns[relationColumn.PropertyName].ColumnName;

                                ITable t = attributeReader.ReadTableAttribute(relation.PropertyAdapter.PropertyType);
                                if (t == null)
                                {
                                    throw ExceptionFactory.AttributeNotFoundException(relation.PropertyAdapter.PropertyType.Type, typeof(TableAttribute));
                                }
                                string foreignColumnName = t.Columns[relationColumn.ForeignPropertyName].ColumnName;
                                relationColumn.ColumnName = columnName;
                                relationColumn.ForeignColumnName = foreignColumnName;
                            }
                        }
                        table.CreateCache();
                        tables.Add(entityType, table);
                    }
                }
            }
            return tables[entityType];
        }
        
        //public abstract IColumn CreateCountColumn();

        public virtual IStatement GetStatementByType(EntityType statementType)
        { 
            if (!statements.ContainsKey(statementType))
            {
                lock (statements)
                {
                    if (!statements.ContainsKey(statementType))
                    {
                        IStatement command = attributeReader.ReadStatementAttribute(statementType);
                        if (command == null)
                        {
                            throw ExceptionFactory.AttributeNotFoundException(statementType.Type, typeof(StatementAttribute));
                        }
                        command.CreateCache();
                        statements.Add(statementType, command);
                    }
                }
            }
            return statements[statementType];
        }

        public void ExecuteReaderAndFill(IDbSession session, object obj, IList[] lists, EntityType[] entityTypes)
        {
            EntityType commandType = new EntityType(obj);
            IStatement statement = GetStatementByType(commandType);

            IDbCommand dbCommand = CreateCommand(session, obj);

            #region reader
            //IDataReader reader = session.ExecuteReader(dbCommand, statement.CommandBehavior);

            //for (int i = 0; i < lists.Length; i++)
            //{
            //    if (i > 0)
            //    {
            //        if (!reader.NextResult())
            //        {
            //            break;
            //        }
            //    }

            //    IList list = lists[i];
            //    Type entityType=list.GetType().GetGenericArguments()[0];
            //    ITable table = GetTableByEntityType(entityType);


            //    //映射中的字段名和实际字段名的交集
            //    Dictionary<string, IColumn> names = new Dictionary<string, IColumn>(StringComparer.OrdinalIgnoreCase);
            //    for (int fieldIndex = 0; fieldIndex < reader.FieldCount; fieldIndex++)
            //    {
            //        string name=reader.GetName(fieldIndex) ;
            //        if (table.Columns.ContainsKey(name) && !names.ContainsKey(name))
            //        {
            //            names.Add(name,table.Columns[name]);
            //        }
            //    }

            //    while (reader.Read())
            //    {
            //        object entityObj = Activator.CreateInstance(entityType);
            //        foreach (KeyValuePair<string,IColumn> kv in names)
            //        {
            //            object v = reader[kv.Key];
            //            kv.Value.PropertyAdapter.SetValue(entityObj, v);
            //        }

            //        list.Add(entityObj);
            //    }
            //    //sqlAnalyseResult.ObjectFiller.Fill(this,obj, dataReader);
            //}
            //reader.Close();
            #endregion

            DataSet ds = session.ExecuteDataSet(dbCommand);
            for (int i = 0; i < lists.Length; i++)
            {
                 IList list = lists[i];
                 //Type type = list.GetType().GetGenericArguments()[0];
                 EntityType entityType = entityTypes == null ? new EntityType(list.GetType().GetGenericArguments()[0]) : entityTypes[i];
                 ITable table = GetTableByEntityType(entityType);
                 DataTable dt = ds.Tables[i];



                 //映射中的字段名和实际字段名的交集
                 HashSet<string> columnNames = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
                 foreach (IColumn column in table.Columns.Values)
                 {
                     if (!columnNames.Contains(column.OutputAlias))
                     {
                         columnNames.Add(column.OutputAlias);
                     }
                 }
                 Dictionary<string, IColumn> names = new Dictionary<string, IColumn>(StringComparer.OrdinalIgnoreCase);
                 for (int fieldIndex = 0; fieldIndex < dt.Columns.Count; fieldIndex++)
                 {
                     string name = dt.Columns[fieldIndex].ColumnName;
                     if (columnNames.Contains(name) && !names.ContainsKey(name))
                     {
                         names.Add(name, table.Columns[name]);
                     }
                 }

                 foreach (DataRow dr in dt.Rows)
                 {
                     object entityObj = Activator.CreateInstance(entityType.Type);
                     foreach (KeyValuePair<string, IColumn> kv in names)
                     {
                         object v = dr[kv.Key];
                         kv.Value.PropertyAdapter.SetValue(entityObj, ConvertDbValue(v, kv.Value.PropertyAdapter.PropertyType.Type));
                     }

                     list.Add(entityObj);
                 }
            }

            foreach (IParameter parameter in statement.OutParameters.Values)
            {
                IDataParameter dataParameter = (IDataParameter)dbCommand.Parameters[parameter.ParameterName];
                parameter.PropertyAdapter.SetValue(obj, ConvertDbValue(dataParameter.Value, parameter.PropertyAdapter.PropertyType.Type));
            }
        }

        public virtual IDataReader ExecuteReader(IDbSession session, object obj)
        {
            EntityType commandType = new EntityType(obj);
            IStatement statement = GetStatementByType(commandType);

            IDbCommand dbCommand = CreateCommand(session, obj);
            IDataReader reader = session.ExecuteReader(dbCommand, statement.CommandBehavior);

            foreach (IParameter parameter in statement.OutParameters.Values)
            {
                IDataParameter dataParameter = (IDataParameter)dbCommand.Parameters[parameter.ParameterName];
                parameter.PropertyAdapter.SetValue(obj, ConvertDbValue(dataParameter.Value, parameter.PropertyAdapter.PropertyType.Type));
            }

            return reader;
        }

        public virtual int ExecuteNonQuery(IDbSession session, object obj)
        {
            EntityType commandType = new EntityType(obj);
            IStatement statement = GetStatementByType(commandType);

            IDbCommand dbCommand = CreateCommand(session, obj);
            int returnCount= session.ExecuteNonQuery(dbCommand);
            foreach (IParameter parameter in statement.OutParameters.Values)
            {
                IDataParameter dataParameter=(IDataParameter)dbCommand.Parameters[parameter.ParameterName];
                parameter.PropertyAdapter.SetValue(obj, ConvertDbValue(dataParameter.Value, parameter.PropertyAdapter.PropertyType.Type));
            }
            return returnCount;
        }

        
        
       
        #endregion




        #region 子类可访问的方法

        protected virtual IDbCommand CreateCommand(IDbSession session, object obj)
        {
            EntityType commandType = new EntityType(obj);
            IStatement statement = GetStatementByType(commandType);


            StringBuilder sb = new StringBuilder();
            IDbCommand dbCommand = session.Connection.CreateCommand();
            dbCommand.CommandText = statement.CommandText;
            dbCommand.CommandType = statement.CommandType;

            foreach (IParameter parameter in statement.Parameters.Values)
            { 
                object peropertyValue=parameter.PropertyAdapter.GetValue(obj);
                IDataParameter dataParameter=CreateParameter(parameter.ParameterName,peropertyValue,parameter.PropertyAdapter.PropertyType.Type,parameter.Size);
                dataParameter.Direction = parameter.Direction;
                dbCommand.Parameters.Add(dataParameter);
            }

            return dbCommand;
        }

        /// <summary>
        /// 根据类型获得数据库表名
        /// </summary> 
        /// <param name="entityType">实体类型</param>
        /// <param name="tableMapping">表名映射关系，如果为null则返回默认表名</param>
        /// <returns></returns>
        protected virtual string GetTableName(EntityType entityType,IDictionary<string, string> tableMapping)
        {
            //if (!tableMapping.ContainsKey(entityType))
            //{
            //    throw ExceptionFactory.TypeMissingInTableMapping("tableMapping", entityType);
            //}
            if (tableMapping != null && tableMapping.ContainsKey(entityType.TypeName))
            {
                //如果tableMapping中包含键则返回，否则去缓存中取
                return tableMapping[entityType.TypeName];
            }
            var table = GetTableByEntityType(entityType); 
            return table.TableName; 
        }


        protected virtual IDataParameter AddParameter(IDataParameterCollection parameters, Counter parameterCounter, string parameterNamePrefix, object parameterValue, Type propertyType,int length)
        {
            //object propertyValue = column.PropertyAdapter.GetValue(obj);
            //object objValue = conditionItem.ConditionValue; 
            IDataParameter parameter = CreateParameter(parameterNamePrefix + parameterCounter.CurrentValue, parameterValue,propertyType,length);
            parameters.Add(parameter);
            parameterCounter.AddOne();
            return parameter;
        }


        #region 生成select相关的sql语句

        /// <summary>
        /// 生成一句完整的包含多表关联的 select from where order by 语句
        /// </summary>
        /// <param name="sb">拼接sql语句时会调用sb.Append方法</param>
        /// <param name="parameters">需要参数时，会生成创建参数并添加至本参数</param>
        /// <param name="parameterCounter">生成sql过程中参数计数器,添加parameters集合的参数的名称格式为{parameterNamePrefix}{parameterCounter}</param> 
        /// <param name="parameterNamePrefix">添加parameters集合的参数的名称为{parameterNamePrefix}{parameterCounter}</param>
        /// <param name="parameterNamePrefixInStatement">在语句中需要这个参数时名称格式为{parameterNamePrefixInStatement}{parameterCounter}</param>
        /// <param name="queryExpression">查询表达式</param>
        /// <param name="sqlAnalyseResult">查询表达式解析后的对象</param>
        /// <param name="tableMapping">动态表名称时使用</param>  
        protected void GenerateSelectSql_SelectFromWhereOrder(
            StringBuilder sb,
            IDataParameterCollection parameters, Counter parameterCounter, string parameterNamePrefix, string parameterNamePrefixInStatement,
            IQueryExpression queryExpression, SqlAnalyseResult sqlAnalyseResult, IDictionary<string, string> tableMapping
            )
        {
            int index = 0;


            //select
            sb.Append("select ");
            index = 0;
            foreach (OutputSqlColumn outputSqlColumn in sqlAnalyseResult.SortedOutputSqlColumns)
            {
                if (index > 0) { sb.Append(","); }
                index++;
                sb.Append(outputSqlColumn.TableAliasAndColumnAlias);
            }


            //from where
            GenerateSelectSql_FromWhere(sb, parameters, parameterCounter, parameterNamePrefix, parameterNamePrefixInStatement, queryExpression, sqlAnalyseResult, tableMapping);



            //order
            if (queryExpression.Orders.Count > 0)
            {
                sb.Append(" order by ");

                index = 0;
                foreach (IOrderExpression orderExpression in queryExpression.Orders)
                {
                    if (index > 0) { sb.Append(","); }
                    index++;
                    SqlColumn sqlColumn = sqlAnalyseResult.GetSqlColumn(orderExpression.PropertyName,orderExpression.GroupFunction);
                    sb.Append(sqlColumn.TableAliasAndColumn);
                    if (orderExpression.OrderType == OrderType.Desc)
                    {
                        sb.Append(" desc ");
                    }
                }

            }
        }

        //生成包含关联表的select语句的 from where 部分
        protected void GenerateSelectSql_FromWhere(
            StringBuilder sb,
            IDataParameterCollection parameters, Counter parameterCounter, string parameterNamePrefix, string parameterNamePrefixInStatement,
            IQueryExpression queryExpression, SqlAnalyseResult sqlAnalyseResult, IDictionary<string, string> tableMapping
            )
        {


            int index = 0;


            //from
            sb.Append(" from ");
            sb.Append(GetTableName(sqlAnalyseResult.MasterEntityType, tableMapping));
            sb.Append(" ");
            sb.Append(sqlAnalyseResult.MasterTableNameAlias);



            //join
            foreach (SqlTable sqlTable in sqlAnalyseResult.ForeignTables)
            {
                sb.Append(" left outer join ");
                sb.Append(GetTableName(sqlTable.ForeignEntityType, tableMapping));
                sb.Append(" ");
                sb.Append(sqlTable.ForeignTableNameAlias);

                sb.Append(" on ");
                index = 0;
                foreach (SqlRelationColumn sqlRelationColumn in sqlTable.RelationColumns)
                {
                    if (index > 0) { sb.Append(" and "); }
                    index++;
                    sb.Append(sqlRelationColumn.Expression);

                }
            }

            //where 
            if (queryExpression.Wheres.Count > 0)
            {
                sb.Append(" where ");
                GenerateSelectSql_Where(sb, parameters, parameterCounter, parameterNamePrefix, parameterNamePrefixInStatement, queryExpression.Wheres, sqlAnalyseResult,true);
            }

            //group by 
            if (queryExpression.Groups.Count > 0)
            {
                sb.Append(" group by "); 
                index = 0;
                foreach (IGroupPropertyExpression groupPropertyExpression in queryExpression.Groups)
                {
                    if (index > 0) { sb.Append(","); }
                    index++;
                    SqlColumn sqlColumn = sqlAnalyseResult.GetSqlColumn(groupPropertyExpression.PropertyName, GroupFunction.None);
                    sb.Append(sqlColumn.TableAliasAndColumn); 
                }
            }

            //having
            if (queryExpression.Havings.Count > 0)
            {
                sb.Append(" having ");
                GenerateSelectSql_Where(sb, parameters, parameterCounter, parameterNamePrefix, parameterNamePrefixInStatement, queryExpression.Havings, sqlAnalyseResult, true);
            }



        }


        //生成包含关联表的select语句的 where 部分
        protected void GenerateSelectSql_Where(
            StringBuilder sb,
            IDataParameterCollection parameters, Counter parameterCounter, string parameterNamePrefix, string parameterNamePrefixInStatement,
            IConditionCollection conditionCollection, SqlAnalyseResult sqlAnalyseResult, bool includeTableNameAlias
            )
        {
            int index = 0;

            string andOr = conditionCollection.ConditionRelation == ConditionRelation.And ? " and " : " or ";
            if (conditionCollection.Count > 1) { sb.Append("("); }
            foreach (IConditionExpression condition in conditionCollection)
            {
                if (index > 0) { sb.Append(andOr); }
                index++;
                if (condition is IConditionCollection)
                {
                    GenerateSelectSql_Where(sb, parameters, parameterCounter, parameterNamePrefix, parameterNamePrefixInStatement, (IConditionCollection)condition, sqlAnalyseResult, includeTableNameAlias);
                }
                else
                {
                    IConditionItem conditionItem = (IConditionItem)condition;
                    SqlColumn sqlColumn = sqlAnalyseResult.GetSqlColumn(conditionItem.PropertyName,conditionItem.GroupFunction);
                    GenerateSelectSql_Where_Item(sb, parameters, parameterCounter, parameterNamePrefix, parameterNamePrefixInStatement, conditionItem, sqlColumn, includeTableNameAlias);
                }
            }
            if (conditionCollection.Count > 1) { sb.Append(") "); }
        }


        protected virtual void GenerateSelectSql_Where_Item(
            StringBuilder sb,
            IDataParameterCollection parameters, Counter parameterCounter, string parameterNamePrefix, string parameterNamePrefixInStatement,
            IConditionItem conditionItem, SqlColumn sqlColumn, bool includeTableNameAlias
            )
        {

            //为处理Like自动增加%
            object objValue = conditionItem.ConditionValue;
            switch (conditionItem.ConditionOperator)
            {
                case ConditionOperator.Like:
                case ConditionOperator.NotLike:
                    objValue = "%" + objValue + "%";
                    break;
                case ConditionOperator.RightLike:
                case ConditionOperator.NotRightLike:
                    objValue = "%" + objValue;
                    break;
                case ConditionOperator.LeftLike:
                case ConditionOperator.NotLeftLike:
                    objValue = objValue + "%";
                    break;
                default:
                    break;
            }



            bool hasParameter = false;
            //添加字段名
            sb.Append(includeTableNameAlias ? sqlColumn.TableAliasAndColumn : sqlColumn.Column.ColumnName);
            //添加操作符
            switch (conditionItem.ConditionOperator)
            {
                case ConditionOperator.Equal:
                    if (conditionItem.ConditionValue == null)
                    {
                        sb.Append(" is null");
                    }
                    else
                    {
                        sb.Append("="); 
                        hasParameter = true;
                    }
                    break;
                case ConditionOperator.GreaterThan:
                    sb.Append(">"); 
                    hasParameter = true;
                    break;
                case ConditionOperator.GreaterThanOrEqual:
                    sb.Append(">="); 
                    hasParameter = true;
                    break;
                case ConditionOperator.LessThan:
                    sb.Append("<"); 
                    hasParameter = true;
                    break;
                case ConditionOperator.LessThanOrEqual:
                    sb.Append("<="); 
                    hasParameter = true;
                    break;
                case ConditionOperator.NotEqual:
                    if (conditionItem.ConditionValue == null)
                    {
                        sb.Append(" is not null");
                    }
                    else
                    {
                        sb.Append(" <>"); 
                        hasParameter = true;
                    }
                    break;
                case ConditionOperator.In:
                case ConditionOperator.NotIn:
                    if (conditionItem.ConditionOperator == ConditionOperator.NotIn)
                    {
                        sb.Append(" not");
                    }
                    sb.Append(" in ");
                    if (objValue is IQueryExpression)
                    {
                        hasParameter = true;
                    }
                    else
                    {
                        string v = conditionItem.ConditionValue + "";
                        v = v.Trim();
                        if (!v.StartsWith("("))
                        {
                            sb.Append("(");
                        }
                        sb.Append(v);
                        if (!v.EndsWith(")"))
                        {
                            sb.Append(")");
                        }
                    }
                    break;
                case ConditionOperator.Like:
                case ConditionOperator.LeftLike:
                case ConditionOperator.RightLike:
                    sb.Append(" like "); 
                    hasParameter = true;
                    break;
                case ConditionOperator.NotLike:
                case ConditionOperator.NotLeftLike:
                case ConditionOperator.NotRightLike:
                    sb.Append(" not like ");
                    hasParameter = true;
                    break;
                case ConditionOperator.Custom:
                    sb.Append(conditionItem.ConditionValue);
                    break;
            }
            if (hasParameter)
            {
                if (objValue is IQueryExpression)
                {
                    IQueryExpression query = objValue as IQueryExpression;
                    SqlAnalyseResult sqlAnalyseResult = SqlAnalyzer.Analyse(this, new EntityType(query.Type), query);
                    sb.Append(" (");
                    GenerateSelectSql_SelectFromWhereOrder(sb, parameters, parameterCounter, parameterNamePrefix, parameterNamePrefixInStatement, query, sqlAnalyseResult, null);
                    sb.Append(")");
                }
                else
                {
                    sb.Append(parameterNamePrefixInStatement + parameterCounter.CurrentValue);
                    Type propertyType = sqlColumn.GroupFunction == GroupFunction.Count ? typeof(int) : sqlColumn.Column.PropertyAdapter.PropertyType.Type;
                    int size = sqlColumn.GroupFunction == GroupFunction.Count ? 4 : sqlColumn.Column.Size;
                    AddParameter(parameters, parameterCounter, parameterNamePrefix, objValue, propertyType, size);
                }
            }
        }


        
        #endregion

        #endregion

        
    }
}
